package com.cd.music.utils;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author yxl  余秀良
 * @description: excel工具类
 * @date 17-6-2 下午3:43
 * @address: 四川成都
 * 使用实例：
 * ExcelUtil build = ExcelUtil.newBuilder().setFilePath("/home/yxl/workbook_xls.xls").
 * setSheetName("该字段可选").
 * setTitle("2017年成绩统计表").
 * setColumnNameList(objects).
 * setDataList(objects1).
 * setAppend(true).
 * build();
 * build.writeExcel();
 */
public class ExcelUtil {
    /**
     * 文件存放路径
     */
    private String filePath;
    /**
     * 默认工作表名字
     */
    private String sheetName = "Sheet1";
    /**
     * 表格上面的标题
     */
    private String title = "默认标题";
    /**
     * 每列的名字集合
     */
    private List<String> columnNameList;
    /**
     * 数据集合
     */
    private List<List<Object>> dataList;
    /**
     * 是否追加　默认为false　表示不追加，覆盖之前的内容
     */
    private boolean append = false;
    /**
     * 默认列的大小
     */
    private static final int DEFAULT_COLUMN_SIZE = 30;

    /**
     * @param builder 文件参数
     */
    private ExcelUtil(Builder builder) {
        if (StringUtil.isEmpty(builder.filePath)) {
            throw new RuntimeException("文件路径不能为空");
        }
        this.filePath = builder.filePath;
        if (!StringUtil.isEmpty(builder.sheetName)) {
            this.sheetName = builder.sheetName;
        }
        if (!StringUtil.isEmpty(builder.title)) {
            this.title = builder.title;
        }
        this.columnNameList = builder.columnNameList;
        this.dataList = builder.dataList;
        this.append = builder.append;
    }

    /**
     * 实例化
     *
     * @return Builder
     */
    public static Builder newBuilder() {
        return new Builder();
    }

    public static class Builder {
        /**
         * 文件存放路径
         */
        private String filePath;
        /**
         * 默认工作表名字
         */
        private String sheetName = "Sheet1";
        /**
         * 表格上面的大标题
         */
        private String title;
        /**
         * 每列的名字集合
         */
        private List<String> columnNameList;
        /**
         * 数据集合
         */
        private List<List<Object>> dataList;
        /**
         * 是否追加　默认为false　表示不追加，覆盖之前的内容
         */
        private boolean append = false;

        /**
         * 设置文件存放路径
         *
         * @param filePath 　文件路径
         * @return 返回　Builder类对象
         */
        public Builder setFilePath(String filePath) {
            this.filePath = filePath;
            return this;
        }

        /**
         * 设置工作表名字
         *
         * @param sheetName 　工作表名字
         * @return 返回　Builder类对象
         */
        public Builder setSheetName(String sheetName) {
            this.sheetName = sheetName;
            return this;
        }

        /**
         * 设置标题
         *
         * @param title 　表格标题
         * @return 返回　Builder类对象
         */
        public Builder setTitle(String title) {
            this.title = title;
            return this;
        }

        /**
         * 设置列名字集合
         *
         * @param columnNameList 列名字集合
         * @return 返回　Builder类对象
         */
        public Builder setColumnNameList(List<String> columnNameList) {
            this.columnNameList = columnNameList;
            return this;
        }

        /**
         * 设置列数据集合
         *
         * @param dataList 列数据集合
         * @return 返回　Builder类对象
         */
        public Builder setDataList(List<List<Object>> dataList) {
            this.dataList = dataList;
            return this;
        }

        /**
         * 设置是否追加数据
         *
         * @param append true表示追加，false表示不追加，直接覆盖之前的数据
         * @return 返回　Builder类对象
         */
        public Builder setAppend(boolean append) {
            this.append = append;
            return this;
        }

        /**
         * 实例化 PushParam
         *
         * @return PushParam
         */
        public ExcelUtil build() {
            return new ExcelUtil(this);
        }
    }

    /**
     * 工作表名称
     *
     * @param sheetName 工作表的名字
     * @return 返回标题集合
     */
    private List<String> getTitle(String sheetName) {
        List<String> titleList = new LinkedList<>();
        try {
            File file = new File(this.filePath);
            if (file.exists()) {
                // 声明一个工作薄
                Workbook workBook = new HSSFWorkbook(new FileInputStream(file));
                //获取工作表
                Sheet sheet = workBook.getSheet(sheetName);
                if (sheet != null && !"".equals(sheet.toString())) {
                    //标题开始行数
                    int titleStartRow = 2;
                    //获取标题行
                    Row row = sheet.getRow((titleStartRow - 1));
                    //获取最后一列的列数
                    int lastCellNum = row.getLastCellNum();
                    for (int i = 0; i < lastCellNum; i++) {
                        titleList.add(row.getCell(i).toString());
                    }
                }
            } else {
                throw new RuntimeException("文件不存在");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return titleList;
    }

    /**
     * 获取excel的数据
     *
     * @return 返回获取到的数据
     */
    public List<Map<Object, Object>> readExcel() {
        List<Map<Object, Object>> data = new LinkedList<>();
        try {
            File file = new File(this.filePath);
            if (file.exists()) {
                // 声明一个工作薄
                Workbook workBook = new HSSFWorkbook(new FileInputStream(file));
                //获取工作表
                Sheet sheet = workBook.getSheet(this.sheetName);
                if (sheet != null) {
                    //获取最后一行行号
                    int lastRowNum = sheet.getLastRowNum();
                    List<String> title = getTitle(this.sheetName);
                    //数据开始存放行数
                    int dataStartRow = 3;
                    for (int i = (dataStartRow - 1); i <= lastRowNum; i++) {
                        //获取标题行
                        Row row = sheet.getRow(i);
                        Map<Object, Object> cellMap = new LinkedHashMap<>();
                        for (int j = 0; j < title.size(); j++) {
                            cellMap.put(title.get(j), row.getCell(j));
                        }
                        data.add(cellMap);
                    }
                }
            } else {
                throw new RuntimeException("文件不存在");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     * 创建excel表格
     *
     * @return File
     */
    public File writeExcel() {
        File file = null;
        try {
            file = new File(this.filePath);
            HSSFWorkbook sheets;
            if (file.exists() && this.append) {
                sheets = new HSSFWorkbook(new FileInputStream(file));
            } else {
                sheets = new HSSFWorkbook();
            }
            Map<String, CellStyle> cellStyleMap = styleMap(sheets);
            // 表头样式
            CellStyle headStyle = cellStyleMap.get("head");
            // 正文样式
            CellStyle contentStyle = cellStyleMap.get("content");
            //正文整数样式
            CellStyle contentIntegerStyle = cellStyleMap.get("integer");
            //正文带小数整数样式
            CellStyle contentDoubleStyle = cellStyleMap.get("double");
            // 生成一个表格
            Sheet sheet = sheets.getSheet(this.sheetName);
            setBookStyle(sheets, headStyle, contentStyle, contentIntegerStyle, contentDoubleStyle, sheet);
            OutputStream ops = new FileOutputStream(file);
            sheets.write(ops);
            ops.flush();
            ops.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    /**
     * 创建excel表格
     *
     * @return File
     */
    public OutputStream writeExcelReturnStream() {
        try {
            File file = new File(this.filePath);
            HSSFWorkbook sheets;
            if (file.exists() && this.append) {
                sheets = new HSSFWorkbook(new FileInputStream(file));
            } else {
                sheets = new HSSFWorkbook();
            }
            Map<String, CellStyle> cellStyleMap = styleMap(sheets);
            // 表头样式
            CellStyle headStyle = cellStyleMap.get("head");
            // 正文样式
            CellStyle contentStyle = cellStyleMap.get("content");
            //正文整数样式
            CellStyle contentIntegerStyle = cellStyleMap.get("integer");
            //正文带小数整数样式
            CellStyle contentDoubleStyle = cellStyleMap.get("double");
            // 生成一个表格
            Sheet sheet = sheets.getSheet(this.sheetName);
            setBookStyle(sheets, headStyle, contentStyle, contentIntegerStyle, contentDoubleStyle, sheet);
            return new FileOutputStream(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void setBookStyle(HSSFWorkbook sheets, CellStyle headStyle, CellStyle contentStyle, CellStyle contentIntegerStyle, CellStyle contentDoubleStyle, Sheet sheet) {
        if (sheet == null) {
            sheet = sheets.createSheet(this.sheetName);
            //最新Excel列索引,从0开始
            int lastRowIndex = sheet.getLastRowNum();
            if (lastRowIndex > 0) {
                lastRowIndex++;
            }
            // 设置表格默认列宽度
            sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
            // 合并单元格
            if (this.columnNameList != null && this.columnNameList.size() > 0) {
                if (this.columnNameList.size() > 1) {
                    sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, this.columnNameList.size() - 1));
                }
                // 产生表格标题行
                Row rowMerged = sheet.createRow(lastRowIndex);
                rowMerged.setHeight((short) 800);
                lastRowIndex++;
                Cell mergedCell = rowMerged.createCell(0);
                mergedCell.setCellStyle(headStyle);
                mergedCell.setCellValue(new HSSFRichTextString(this.title));
                // 产生表格表头列标题行
                Row row = sheet.createRow(lastRowIndex);
                row.setHeight((short) 500);
                lastRowIndex++;
                getCell(this.columnNameList, headStyle, row);
                // 遍历集合数据,产生数据行,前两行为标题行与表头行
                setStyle(contentStyle, contentIntegerStyle, contentDoubleStyle, sheet, lastRowIndex);
            }
        } else {
            if (this.append) {
                //最新Excel列索引,从0开始
                int lastRowIndex = sheet.getLastRowNum();
                if (lastRowIndex > 0) {
                    lastRowIndex++;
                }
                // 设置表格默认列宽度
                sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
                // 产生表格表头列标题行
                // 遍历集合数据,产生数据行,前两行为标题行与表头行
                setStyle(contentStyle, contentIntegerStyle, contentDoubleStyle, sheet, lastRowIndex);
            }
        }
    }

    private void setStyle(CellStyle contentStyle, CellStyle contentIntegerStyle, CellStyle contentDoubleStyle, Sheet
            sheet, int lastRowIndex) {
        Row row;
        if (this.dataList != null && this.dataList.size() > 0) {
            for (List<Object> dataRow : this.dataList) {
                row = sheet.createRow(lastRowIndex);
                row.setHeight((short) 500);
                lastRowIndex++;
                for (int j = 0; j < dataRow.size(); j++) {
                    Cell contentCell = row.createCell(j);
                    Object dataObject = dataRow.get(j);
                    if (dataObject != null) {
                        if (dataObject instanceof Integer) {
                            contentCell.setCellType(CellType.NUMERIC);
                            contentCell.setCellStyle(contentIntegerStyle);
                            contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
                        } else if (dataObject instanceof Double) {
                            contentCell.setCellType(CellType.NUMERIC);
                            contentCell.setCellStyle(contentDoubleStyle);
                            contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
                        } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                            contentCell.setCellType(CellType.STRING);
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
                        } else if (dataObject instanceof Date) {
                            contentCell.setCellType(CellType.STRING);
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(getCnDate((Date) dataObject));
                        } else {
                            contentCell.setCellType(CellType.STRING);
                            contentCell.setCellStyle(contentStyle);
                            contentCell.setCellValue(dataObject.toString());
                        }
                    } else {
                        contentCell.setCellStyle(contentStyle);
                        // 设置单元格内容为字符型
                        contentCell.setCellValue("");
                    }
                }
            }
        }
    }

    /**
     * @param columnNames List<String>
     * @param headStyle   CellStyle
     * @param row         Row
     */
    private static void getCell(List<String> columnNames, CellStyle headStyle, Row row) {
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            RichTextString text = new HSSFRichTextString(columnNames.get(i));
            cell.setCellValue(text);
        }
    }

    /**
     * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
     *
     * @param date 需要转换的时间
     * @return 返回时间
     */
    private static String getCnDate(Date date) {
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }

    /**
     * 创建单元格表头样式
     *
     * @param workbook 工作薄
     */
    private static CellStyle createCellHeadStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 表头样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        font.setFontHeightInPoints((short) 18);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 单元格样式列表
     *
     * @param workbook Workbook
     * @return Map
     */
    private static Map<String, CellStyle> styleMap(Workbook workbook) {
        Map<String, CellStyle> styleMap = new LinkedHashMap<>();
        styleMap.put("head", createCellHeadStyle(workbook));
        styleMap.put("content", createCellContentStyle(workbook));
        styleMap.put("integer", createCellContent4IntegerStyle(workbook));
        styleMap.put("double", createCellContent4DoubleStyle(workbook));
        return styleMap;
    }

    /**
     * 设置央视
     *
     * @param workbook Workbook
     * @return CellStyle
     */
    private static CellStyle createCellContent4DoubleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(FillPatternType.NO_FILL);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        //保留两位小数点
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        return style;
    }

    /**
     * @param workbook Workbook
     * @return CellStyle
     */
    private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(FillPatternType.NO_FILL);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        //数据格式只显示整数
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        return style;
    }

    /**
     * @param workbook Workbook
     * @return CellStyle
     */
    private static CellStyle createCellContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //设置对齐样式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(FillPatternType.NO_FILL);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

}
